using Newtonsoft.Json;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using T.FAS.Runtime.Action.Service;
using T.FAS.Runtime.Action.SPI;
using T.FAS.Runtime.Base.Common;
using T.FAS.Runtime.Base.Entity;
using T.FAS.Runtime.Excel.Dao;
using T.FAS.Runtime.Excel.Entity;
using T.FAS.Runtime.Excel.SPI;
using T.FAS.Runtime.Metadata.Entity.DataModel;
using T.FAS.Runtime.Expression.Entity;
using T.FAS.Runtime.Expression.Service;
using T.FAS.Runtime.Metadata.Client;
using T.ServiceComponent.Kernel.Service;
using AppContext = T.ServiceComponent.Session.Service.AppContext;

namespace T.FAS.Runtime.Excel.Service
{
    public class ImportExecutor : IImportExecutor
    {
        private readonly ImportTaskLogic importTaskLogic = new ImportTaskLogic();
        private readonly object lockobj = new object();
        /// <summary>
        /// 导入确认
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, object> ImportConfirm(string fileURL, bool isEnableExplainRow, List<ImportDef> importDefs)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            try
            {
                var task = new ImportTask { FileURL = fileURL };
                var workbook = FileUtil.GetWorkbook(task);
                var sheets = new List<object>();
                for (int i = 0; i < workbook.NumberOfSheets; i++)//返回Excel Sheet列表
                {
                    ISheet sheet = workbook.GetSheetAt(i);
                    int isAdd = importDefs.Count(t => t.ExcelColName == sheet.SheetName);
                    if (isAdd == 0)
                    {
                        int row = isEnableExplainRow ? sheet.LastRowNum - 1 : sheet.LastRowNum;
                        sheets.Add(new
                        {
                            sheet.SheetName,
                            RowNum = row
                        });
                        if (ConfigUtil.Instance.ImportMaxRow > 0 && ConfigUtil.Instance.ImportMaxRow < row)
                        {
                            throw new FasException(ExcelConstCode.ImportFailed, T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_491881740", "超过最大导入行数{0}，请分批次导入", ConfigUtil.Instance.ImportMaxRow));
                        }
                    }
                }
                dic.Add("state", "1");
                dic.Add("sheets", sheets);
            }
            catch (Exception e)
            {
                dic.Add("state", "0");
                dic.Add("errmsg", e.Message);
            }
            return dic;
        }

        /// <summary>
        /// 导入
        /// </summary>
        public ImportTask ImportData(ImportParam importParam, List<ImportDef> importDefs, List<ImportVariable> importVariables)
        {
            if(ConfigUtil.Instance.ImportMaxRow > 0 && importParam.MaxRow > ConfigUtil.Instance.ImportMaxRow)
            {
                importParam.MaxRow = ConfigUtil.Instance.ImportMaxRow;
            }
            string userName = Base.Common.UserUtil.GetCurrentUserName();
            string userId = Base.Common.UserUtil.GetCurrentUserID();
            List<ImportTaskDetail> oldImportTaskDetails = null;
            ImportTask task = InitImportTask(importParam, userName, userId, ref oldImportTaskDetails);
            var context = ContextHelper.GetContext().Clone() as DefaultContext;

            Task.Run(() =>
            {
                DateTime startTime = DateTime.Now;
                try
                {
                    ContextHelper.SetContext(context.Clone() as DefaultContext);
                    var cdo = MetaDataClientService.GetInstance().GetMetaDataContent(importParam.CDOID) as CompositeDataObject;//获取CDO
                    if (cdo == null)
                        throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_879186205", "获取元数据失败:")}" + importParam.CDOID);
                    IWorkbook work = FileUtil.GetWorkbook(task);
                    var inputParam = GetGlobalVariable(importVariables); //获取全局变量
                    GetImportTaskDetails(cdo, work, importDefs, task, importParam, oldImportTaskDetails, inputParam); //导入明细
                    if (task.FailedCount == 0) //成功
                        task.State = 1;
                    else if (task.SuccessCount == 0) //失败
                        task.State = 3;
                    else //部分成功
                        task.State = 2;
                }
                catch (Exception e)
                {
                    task.State = 3;
                    task.ErrMsg = ExceptionUtil.GetExceptionMsg(e);
                }
                task.LastModifyTime = DateTime.Now;
                task.LastModifier = userName;
                task.ElapsedSeconds = (task.LastModifyTime - startTime).TotalSeconds;
                importTaskLogic.UpdateTask(task);
            });
            return task;
        }
        /// <summary>
        /// 初始化导入任务
        /// </summary>
        /// <returns></returns>
        public ImportTask InitImportTask(ImportParam importParam, string userName, string userId, ref List<ImportTaskDetail> oldImportTaskDetails)
        {
            ImportTask task;
            if (!string.IsNullOrEmpty(importParam.TaskID))
            {

                task = importTaskLogic.GetTask(importParam.TaskID);
                if (task == null)
                    throw new FasException(ExcelConstCode.InValidParam, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1650304169", "任务不存在")}");
                task.FilePath = importParam.FileName;
                task.FileURL = importParam.FileURL;
                task.FileExts = Path.GetExtension(task.FileURL).ToLower();
                if (task.FileName != importParam.FileName)
                    throw new FasException(ExcelConstCode.RetryFailed, T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_655383797", "重试文件名要相同，源文件名：【{0}】，新文件名：【{1}】", task.FileName, importParam.FileName));
                task.FileName = importParam.FileName;
                task.UserID = userId;
                task.Script = importParam.Script;
                task.State = 0;
                task.LastModifier = userName;
                task.LastModifyTime = DateTime.Now;
                task.ErrMsg = "";
                task.SuccessCount = 0;
                task.FailedCount = 0;
                importTaskLogic.UpdateTask(task);
                oldImportTaskDetails = importTaskLogic.GetTaskDetailList(task.ID);
            }
            else
            {
                if (!importParam.CanRepeatImport)
                {
                    bool isImport = importTaskLogic.CheckIsImportByFileName(importParam.CDOID, userId, importParam.FileName);
                    if (isImport)
                        throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1787264856", "当前文件已导入过")}");
                }
                task = InitImportTask(importParam, userName, userId); //初始化导入任务
            }

            return task;
        }
        /// <summary>
        /// 初始化导入任务
        /// </summary>
        /// <returns></returns>
        private ImportTask InitImportTask(ImportParam importParam, string userName, string userId)
        {
            ImportTask task = new ImportTask();
            task.ID = Guid.NewGuid().ToString();
            task.CDOID = importParam.CDOID;
            task.FileExts = Path.GetExtension(importParam.FileURL).ToLower();
            task.FileName = importParam.FileName;
            task.FilePath = importParam.FileName;
            task.FileURL = importParam.FileURL;
            task.CreateTime = DateTime.Now;
            task.Creator = userName;
            task.LastModifier = userName;
            task.LastModifyTime = DateTime.Now;
            task.UserID = userId;
            task.Script = importParam.Script;
            importTaskLogic.CreateTask(task);
            return task;
        }

        /// <summary>
        /// 执行全局变量表达式构造表达式入参
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, object> GetGlobalVariable(List<ImportVariable> importVariables)
        {
            var inputParam = new Dictionary<string, object>();
            var keyValues = new Dictionary<string, object>();
            foreach (var item in importVariables)
            {
                if (item.ParamType != 0)//前端表达式
                {
                    keyValues[item.VarName] = item.ParamValue;
                }
                else if (!string.IsNullOrEmpty(item.Script))
                {
                    ExprResult importVariable = ExprService.GetInstance.JSExecuteNotReport(item.Script, new Dictionary<string, object>());
                    if (importVariable.State == ResultState.Fail)
                        throw new FasException(ExcelConstCode.ImportFailed, importVariable.ErrMsg);
                    keyValues[item.VarName] = importVariable.Result;
                }
            }
            inputParam["InputParam"] = keyValues;
            return inputParam;
        }

        /// <summary>
        /// 导入数据并生成任务明细
        /// </summary>
        /// <returns></returns>
        public void GetImportTaskDetails(CompositeDataObject cdo, IWorkbook workbook, List<ImportDef> importDefs, ImportTask task, ImportParam importParam, List<ImportTaskDetail> oldImportTaskDetails, Dictionary<string, object> inputParam)
        {
            //获取ExcelSheet页
            if (workbook.NumberOfSheets == 0)
                throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_58723196", "Excel不能为空")}");
            ISheet sheet;
            if (string.IsNullOrEmpty(importParam.SheetName))
                sheet = workbook.GetSheetAt(0); //CDO Sheet页
            else
                sheet = workbook.GetSheet(importParam.SheetName); //CDO Sheet页
            int lastRowNum = sheet.LastRowNum;
            int firstRow = 1;
            if (importParam.IsEnableExplainRow)
                firstRow = 2;
            if (sheet.LastRowNum < firstRow)
                throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_570299930", "导入内容不能为空")}");
            if (sheet.LastRowNum > importParam.MaxRow + 1)
                throw new FasException(ExcelConstCode.ImportFailed, T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_491881740", "超过最大导入行数{0}，请分批次导入", importParam.MaxRow));
            //实际导入定义
            bool needUniqueCheck = GetImportDefs(sheet, importDefs);
            DataQuery dataQuery = null;
            string mainObjectAlias = "";
            if (needUniqueCheck)
            {
                if (string.IsNullOrEmpty(importParam.DataQueryID))
                    throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1580455015", "数据查询ID不能为空")}");
                //需要重复校验 获取DQ
                dataQuery = GetDataQuery(importParam.DataQueryID, needUniqueCheck, out mainObjectAlias);
            }
            //帮助数据字典获取
            var dic = GetDataSource(importDefs);
            //遍历Excel数据
            var headerRow = sheet.GetRow(0);
            if (headerRow == null)
                throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1449733063", "第一行不能为空！")}");
            if (importParam.IsEnableUniqueCol)
            {
                var firstCell = headerRow.GetCell(0);
                if (firstCell == null || firstCell.StringCellValue != $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1318759965", "唯一标识")}")
                    throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1791040997", "第一列应为唯一标识列！")}");
            }
            var actionService = ActionExecuteService.GetInstance;
            List<string> importUniqueIDs = new List<string>(); //导入任务明细
            task.FailedCount = 0;
            for (var i = firstRow; i <= lastRowNum; i++)
            {
                InsertRecord(cdo, workbook, importDefs, task, importParam, oldImportTaskDetails, inputParam, sheet, needUniqueCheck, dataQuery, mainObjectAlias, dic, actionService, importUniqueIDs, i);
            }
        }

        public void InsertRecord(CompositeDataObject cdo, IWorkbook workbook, List<ImportDef> importDefs, ImportTask task, ImportParam importParam, List<ImportTaskDetail> oldImportTaskDetails, Dictionary<string, object> inputParam, ISheet sheet, bool needUniqueCheck, DataQuery dataQuery, string mainObjectAlias, Dictionary<string, Dictionary<string, object>> dic, ActionExecuteService actionService, List<string> importUniqueIDs, int i)
        {
            //任务明细
            ImportTaskDetail importTaskDetail = new ImportTaskDetail();
            importTaskDetail.ID = Guid.NewGuid().ToString();
            importTaskDetail.RowNum = (i + 1);
            importTaskDetail.TaskID = task.ID;
            //初始化TRecord
            TRecord record = new TRecord();
            try
            {
                var row = sheet.GetRow(i);
                if (row == null)
                    throw new FasException(ExcelConstCode.ImportFailed, "数据不存在"); //没有数据的行默认是null
                CheckUniqueCol(task, importParam, oldImportTaskDetails, importUniqueIDs, importTaskDetail, row);

                var parameters = new List<ActionParameter>();
                parameters.Add(new ActionParameter { ParameterName = "TRecord", Order = 0, ParameterValue = record });
                TResult tResult = actionService.Execute(cdo.ID, Action.SPI.ActionType.CDO_NewInstance, parameters, false);

                var result = tResult as TCommonResult<TRecord>;
                record = result.Record;

                var eva = FileUtil.GetEvaluator(workbook);
                //生成单条记录
                record = GetTRecord(record, row, importDefs, eva, dic, mainObjectAlias);
                parameters = ExecuteJS(record, importParam.Script, inputParam);
                Group group = GetGroup(record, importDefs, mainObjectAlias);
                //校验数据重复
                TRecord oldRecord = CheckUniqueData(dataQuery, needUniqueCheck, group);
                if (oldRecord == null)
                {
                    //插入新数据
                    actionService.Execute(cdo.ID, Action.SPI.ActionType.CDO_Create, parameters, false);
                }
                else
                {
                    if (importParam.ImportStrategy == 1)
                        throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_111625350", "重复性校验失败")}");
                    //更新已有数据
                    DateTime time = Convert.ToDateTime(oldRecord.GetValue("LastModifyTime"));
                    string id = Convert.ToString(oldRecord.GetValue("ID"));
                    Timestamp timestamp = new Timestamp { Data = time.ToString(Timestamp.DefaultTimeFormat), Value = time };
                    record = GetTRecord(oldRecord, row, importDefs, eva, dic, mainObjectAlias);
                    record.SetValue("LastModifyTime", timestamp);
                    record.SetValue("ID", id);
                    parameters = ExecuteJS(record, importParam.Script, inputParam);
                    actionService.Execute(cdo.ID, Action.SPI.ActionType.CDO_Update, parameters, false);
                }
                task.SuccessCount++;
                importTaskDetail.State = 1; //成功
            }
            catch (Exception ex)
            {
                task.FailedCount++;
                importTaskDetail.State = 0; //失败
                Base.Common.LogUtil.Log(ex.ToString(), "Excel");
                importTaskDetail.ErrMsg = ExceptionUtil.GetExceptionMsg(ex).Replace("<br />", "");
            }
            importTaskLogic.UpdateTask(task);
            importTaskLogic.DeleteTaskDetail(importTaskDetail.ID);
            importTaskLogic.CreateTaskDetail(new List<ImportTaskDetail> { importTaskDetail });
        }
        /// <summary>
        /// 校验唯一标识列
        /// </summary>
        public void CheckUniqueCol(ImportTask task, ImportParam importParam, List<ImportTaskDetail> oldImportTaskDetails, List<string> importUniqueIDs, ImportTaskDetail importTaskDetail, IRow row)
        {
            if (importParam.IsEnableUniqueCol)
            {
                //校验唯一标识
                var cell = row.GetCell(0);
                if (cell == null)
                    throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1720527445", "唯一标识不能为空；")}");
                switch (cell.CellType)
                {
                    case CellType.String:
                        importTaskDetail.UniqueID = cell.StringCellValue;
                        break;
                    case CellType.Numeric:
                        importTaskDetail.UniqueID = cell.NumericCellValue.ToString();
                        break;
                    default:
                        throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_233291713", "唯一标识类型不对，只能为字符串或数值")}");
                }
                lock (lockobj)
                {
                    int hasUnique = importUniqueIDs.Count(t => t == importTaskDetail.UniqueID);
                    if (hasUnique > 0)
                        throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_473100229", "唯一标识列在Excel中重复")}");
                    importUniqueIDs.Add(importTaskDetail.UniqueID);
                }
                var oldImportTaskDetail = oldImportTaskDetails?.FirstOrDefault(t => t.UniqueID == importTaskDetail.UniqueID);
                if (oldImportTaskDetail != null)
                {
                    if (oldImportTaskDetail.State == 1)
                    {
                        task.SuccessCount++;
                        oldImportTaskDetail.ErrMsg = $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1093522353", "数据已导入过")}";
                        importTaskLogic.UpdateTaskDetail(oldImportTaskDetail);
                    }
                    else
                    {
                        importTaskDetail.ID = oldImportTaskDetail.ID;
                    }
                }
            }
        }

        /// <summary>
        /// 获取实际导入定义
        /// </summary>
        /// <returns></returns>
        public bool GetImportDefs(ISheet sheet, List<ImportDef> importDefs)
        {
            IRow headerRow = sheet.GetRow(0); //获取标题行
            var colInfos = new Dictionary<string, int>();
            for (int i = 0; i < headerRow.LastCellNum; i++)
            {
                var cell = headerRow.GetCell(i);
                if (cell != null)
                    colInfos[cell.ToString().Trim()] = i;
            }
            //获取第一行标题列,对应为导入定义的标题列
            bool needUniqueCheck = false; //是否需要校验
            for (int i = 0; i < importDefs.Count; i++)
            {
                if (importDefs[i].ImportRule == 2)
                    continue;
                if (!colInfos.ContainsKey(importDefs[i].ExcelColName))
                    throw new FasException(ExcelConstCode.ImportFailed, T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_259101357", "列【{0}】不存在", importDefs[i].ExcelColName));
                importDefs[i].ColNum = colInfos[importDefs[i].ExcelColName];
                if (importDefs[i].IsUnique)
                    needUniqueCheck = true;
            }
            return needUniqueCheck;
        }
        /// <summary>
        /// 获取取数范围的内容
        /// </summary>
        /// <param name = "importDefs"></param>
        /// <returns></returns>
        public Dictionary<string, Dictionary<string, object>> GetDataSource(List<ImportDef> importDefs)
        {
            var dic = new Dictionary<string, Dictionary<string, object>>();
            foreach (var importDef in importDefs)
            {
                if (!string.IsNullOrEmpty(importDef.ActionID) && importDef.IsHelp)
                {
                    HandleNewHelp(dic, importDef);//新版本导入帮助处理
                }
                if (!string.IsNullOrEmpty(importDef.DataSource) && importDef.IsHelp)
                {
                    HandleOldHelp(dic, importDef);//老版本导入帮助处理
                }
            }
            return dic;
        }
        /// <summary>
        /// 老版本导入帮助处理
        /// </summary>
        public void HandleOldHelp(Dictionary<string, Dictionary<string, object>> dic, ImportDef importDef)
        {
            var tempDic = new Dictionary<string, object>();
            if (string.IsNullOrEmpty(importDef.ColName) || string.IsNullOrEmpty(importDef.DisplayField) || string.IsNullOrEmpty(importDef.ValueField))
                throw new FasException(ExcelConstCode.ImportFailed, T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_8846868", "列【{0}】导入定义错误", importDef.ExcelColName));
            var sdo = new Dictionary<string, object>();
            ExprResult exprResult = ExprService.GetInstance.JSExecuteNotReport(importDef.DataSource, tempDic); // 执行取数范围表达式
            if (exprResult.State == ResultState.Fail)
                throw new FasException(ExcelConstCode.ImportFailed, importDef.DataSource + $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_253206119", "执行失败")}" + exprResult.ErrMsg);
            var array = exprResult.Result as TArray<TRecord>;
            if (array == null)
                throw new FasException(ExcelConstCode.ImportFailed, importDef.DataSource + $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_309835851", "返回类型必须为集合")}");
            foreach (var item in array) //数据源定义查询
            {
                string key = Convert.ToString(item.GetValue(importDef.DisplayField));
                if (string.IsNullOrEmpty(key))
                    continue;
                object value = item.GetValue(importDef.ValueField);
                if (value == null)
                    continue;
                sdo[key] = value;
            }
            dic[importDef.ColName] = sdo;
        }
        /// <summary>
        /// 新版本导入处理
        /// </summary>
        public void HandleNewHelp(Dictionary<string, Dictionary<string, object>> dic, ImportDef importDef)
        {
            if (string.IsNullOrEmpty(importDef.ColName) || string.IsNullOrEmpty(importDef.DisplayField) || string.IsNullOrEmpty(importDef.ValueField))
                throw new FasException(ExcelConstCode.ImportFailed, T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_8846868", "列【{0}】导入定义错误", importDef.ExcelColName));
            var sdo = new Dictionary<string, object>();
            TResult tResult;
            switch (importDef.ActionType)
            {
                case 2:
                    tResult = ActionExecuteService.GetInstance.Execute(importDef.ActionID, Action.SPI.ActionType.Action, importDef.Parameters, true);
                    break;
                case 3:
                    tResult = ActionExecuteService.GetInstance.Execute(importDef.ActionID, Action.SPI.ActionType.DQ, importDef.Parameters, true);
                    break;
                default:
                    throw new FasException(ExcelConstCode.ExportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1232038745", "不支持的Action类型")}");
            }
            var result = tResult as TCommonResult<TRecord>;
            var array = result.List;
            if (array == null)
                throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_948686448", "帮助返回类型必须为集合")}");
            foreach (var item in array) //数据源定义查询
            {
                string key = Convert.ToString(item.GetValue(importDef.DisplayField));
                if (string.IsNullOrEmpty(key))
                    continue;
                object value = item.GetValue(importDef.ValueField);
                if (value == null)
                    continue;
                sdo[key] = value;
            }
            dic[importDef.ColName] = sdo;
        }

        /// <summary>
        /// 获取DQ
        /// </summary>
        /// <param name = "dataQueryID"></param>
        /// <param name = "needUniqueCheck"></param>
        /// <param name = "mainObjectAlias"></param>
        /// <returns></returns>
        public DataQuery GetDataQuery(string dataQueryID, bool needUniqueCheck, out string mainObjectAlias)
        {
            mainObjectAlias = "";
            DataQuery dataQuery = new DataQuery();
            if (needUniqueCheck)
            {
                dataQuery = MetaDataClientService.GetInstance().GetMetaDataContent(dataQueryID) as DataQuery;
                if (dataQuery == null)
                    throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_218912532", "获取DQ元数据失败")}" + dataQueryID);
                if (dataQuery.DataQuerySources == null)
                    throw new FasException(ExcelConstCode.ImportFailed, dataQuery.Code + $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1939276348", "DQ元数据没有数据源")}");
                var dataQuerySource = dataQuery.DataQuerySources.FirstOrDefault(t => t.IsMainSource);
                if (dataQuerySource == null)
                    throw new FasException(ExcelConstCode.ImportFailed, dataQuery.Code + $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_279318776", "DQ元数据没有主数据源")}");
                mainObjectAlias = dataQuerySource.Alias; //DQ主DO别名
            }
            return dataQuery;
        }

        /// <summary>
        /// 校验数据是否重复
        /// </summary>
        /// <param name = "sheet"></param>
        public TRecord CheckUniqueData(DataQuery dataQuery, bool needUniqueCheck, Group group)
        {
            if (needUniqueCheck)
            {
                var parameters = new List<ActionParameter>();
                var param = new[] { "DefaultInputFilter", "PageIndex", "PageSize", "AdvancedFilter", "AdvancedSort" };
                parameters.Add(new ActionParameter { ParameterName = "DefaultInputFilter", ParameterValue = "" });
                parameters.Add(new ActionParameter { ParameterName = "PageIndex", ParameterValue = 0 });
                parameters.Add(new ActionParameter { ParameterName = "PageSize", ParameterValue = 0 });
                parameters.Add(new ActionParameter { ParameterName = "AdvancedFilter", ParameterValue = JsonConvert.SerializeObject(group) });
                parameters.Add(new ActionParameter { ParameterName = "AdvancedSort", ParameterValue = null });
                foreach (var item in dataQuery.DataQueryParams)
                {
                    if (!param.Contains(item.Code))
                        parameters.Add(new ActionParameter { ParameterName = item.Code, ParameterValue = "" });
                }
                TResult result = ActionExecuteService.GetInstance.Execute(dataQuery.ID, Action.SPI.ActionType.DQ, parameters, false);
                var array = result as TCommonResult<TRecord>;
                if (result.Count > 1)
                    throw new FasException(ExcelConstCode.ImportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1601758031", "重复性校验失败，存在多条数据")}");
                else if (result.Count == 1)
                    return array.List[0];
            }

            return null;
        }

        /// <summary>
        /// 生成单条记录
        /// </summary>
        /// <returns></returns>
        public TRecord GetTRecord(TRecord record, IRow row, List<ImportDef> realimportDefs, IFormulaEvaluator eva, Dictionary<string, Dictionary<string, object>> dic, string mainObjectAlias)
        {
            StringBuilder errmsg = new StringBuilder();
            var inputParam = new Dictionary<string, object>();
            for (int j = 0; j < realimportDefs.Count; j++)
            {
                ImportDef importDef = realimportDefs[j];
                switch (importDef.ImportRule)
                {
                    case 1:
                        break;
                    case 2:
                        if (!string.IsNullOrEmpty(importDef.Script))
                        {
                            ExprResult exprResult = ExprService.GetInstance.JSExecuteNotReport(importDef.Script, inputParam);
                            if (exprResult.State == ResultState.Fail)
                                throw new FasException(ExcelConstCode.ImportFailed, exprResult.ErrMsg);
                            record.SetValue(importDef.ColName, exprResult.Result);
                        }
                        break;
                    default:
                        var cell = row.GetCell(importDef.ColNum);
                        if (cell == null) //单元格内容非空验证
                        {
                            if(importDef.IsRequired)
                                throw new FasException(ExcelConstCode.ImportFailed, T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_1397814567", "列【{0}】不能为空", importDef.ExcelColName));
                            else
                                record.SetValue(importDef.ColName, "");
                        }
                        else
                        {
                            SetProperty(record, eva, dic, errmsg, importDef, cell);
                        }
                        break;
                }
            }
            if (!string.IsNullOrEmpty(errmsg.ToString()))
                throw new FasException(ExcelConstCode.ImportFailed, errmsg.ToString());
            return record;
        }

        /// <summary>
        /// 获取唯一性校验条件
        /// </summary>
        /// <returns></returns>
        public Group GetGroup(TRecord record, List<ImportDef> realimportDefs, string mainObjectAlias)
        {
            Group group = new Group();
            for (int j = 0; j < realimportDefs.Count; j++)
            {
                ImportDef importDef = realimportDefs[j];
                if (importDef.ImportRule == 1)
                    continue;
                if (importDef.IsUnique) //是否校验唯一
                {
                    var list = new List<string>();
                    var value = Convert.ToString(record.GetValue(importDef.ColName));
                    if (string.IsNullOrEmpty(value))
                        throw new FasException(ExcelConstCode.ImportFailed, T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_1397814567", "列【{0}】不能为空", importDef.ExcelColName));
                    list.Add(value);
                    Filter filter = new Filter { Code = mainObjectAlias + "." + importDef.ColName, Name = importDef.ExcelColName, Condition = "=", Value = list };
                    if (group.Filters == null) //添加高级查询标识
                        group.AddFilter(filter);
                    else
                        group.AddFilter(new FRelation { Relation = "and" }, filter);
                }
            }
            return group;
        }
        /// <summary>
        /// 设置属性
        /// </summary>
        public void SetProperty(TRecord record, IFormulaEvaluator eva, Dictionary<string, Dictionary<string, object>> dic, StringBuilder errmsg, ImportDef importDef, ICell cell)
        {
            switch (cell.CellType)
            {
                //首先在NPOI中数字和日期都属于Numeric类型
                //通过NPOI中自带的DateUtil.IsCellDateFormatted判断是否为时间日期类型
                case CellType.Numeric when DateUtil.IsCellDateFormatted(cell):
                    DateTime dateTime = cell.DateCellValue;
                    if (dateTime.Year == 1899)
                    {
                        record.SetValue(importDef.ColName, dateTime.ToString("HH:mm:ss"));
                    }
                    else
                    {
                        record.SetValue(importDef.ColName, dateTime);
                    }
                    break;
                case CellType.Numeric:
                    //其他数字类型
                    record.SetValue(importDef.ColName, cell.NumericCellValue);
                    break;
                //空数据类型
                case CellType.Blank:
                    if (importDef.IsRequired)
                        errmsg.Append(T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_1578247740", "列【{0}】不能为空；", importDef.ExcelColName));
                    record.SetValue(importDef.ColName, "");
                    break;
                //公式类型
                case CellType.Formula:
                    var cellValue = eva.Evaluate(cell);
                    switch (cellValue.CellType)
                    {
                        case CellType.Numeric:
                            record.SetValue(importDef.ColName, eva.Evaluate(cell).NumberValue);
                            break;
                        case CellType.Boolean:
                            record.SetValue(importDef.ColName, eva.Evaluate(cell).BooleanValue);
                            break;
                        case CellType.Error:
                            errmsg.Append(T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_1250311153", "列【{0}】数据计算错误；", importDef.ExcelColName));
                            break;
                        default:
                            record.SetValue(importDef.ColName, eva.Evaluate(cell).StringValue);
                            break;
                    }
                    break;
                //布尔类型
                case CellType.Boolean:
                    record.SetValue(importDef.ColName, cell.BooleanCellValue);
                    break;
                //错误
                case CellType.Error:
                    errmsg.Append(T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_38871714", "列【{0}】数据错误；", importDef.ExcelColName));
                    break;
                //其他类型都按字符串类型来处理（未知类型CellType.Unknown，字符串类型CellType.String）
                default:
                    if ((!string.IsNullOrEmpty(importDef.ActionID) || !string.IsNullOrEmpty(importDef.DataSource)) && !string.IsNullOrEmpty(importDef.ValueField) && importDef.IsHelp)//帮助类型取值
                    {
                        if (dic[importDef.ColName].ContainsKey(cell.StringCellValue))
                            record.SetValue(importDef.ColName, dic[importDef.ColName][cell.StringCellValue]);
                        else
                            errmsg.Append(importDef.ExcelColName + cell.StringCellValue + $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1117924994", "不存在；")}");
                    }
                    else
                    {
                        if (importDef.IsRequired && string.IsNullOrEmpty(cell.StringCellValue))
                            errmsg.Append(T.ServiceComponent.I18N.Service.TermService.Current.GetTextFormatted("T_FAS_fasr_Excel_1578247740", "列【{0}】不能为空；", importDef.ExcelColName));
                        record.SetValue(importDef.ColName, cell.StringCellValue);
                    }
                    break;
            }
        }

        /// <summary>
        /// 执行导入表达式
        /// </summary>
        /// <returns></returns>
        public List<ActionParameter> ExecuteJS(TRecord record, string script, Dictionary<string, object> inputParam)
        {
            //执行脚本
            if (!string.IsNullOrEmpty(script))
            {
                FASContext fASContext = new FASContext { CurrentRecord = record };

                if (inputParam == null)
                    inputParam = new Dictionary<string, object>();
                inputParam["FASContext"] = fASContext;
                ExprResult exprResult = ExprService.GetInstance.JSExecuteNotReport(script, inputParam);
                if (exprResult.State == ResultState.Fail)
                    throw new FasException(ExcelConstCode.ImportFailed, exprResult.ErrMsg);
            }
            //执行数据插入
            var parameters = new List<ActionParameter>();
            parameters.Add(new ActionParameter { ParameterName = "TRecord", Order = 0, ParameterValue = record });
            return parameters;
        }

        /// <summary>
        /// 获取Excel模板
        /// </summary>
        /// <returns></returns>
        public IWorkbook GetWorkbook(ImportParam importParam, CompositeDataObject cdo, List<ImportDef> importDefs)
        {
            IWorkbook workbook = FileUtil.GetWorkbook();
            #region 基础信息
            ICellStyle headStyle = workbook.CreateCellStyle(); //表头格式
            headStyle.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 14;
            font.IsBold = true;
            headStyle.SetFont(font);
            #endregion
            #region CDOSheet页
            ISheet sheet = workbook.CreateSheet("Sheet0");
            IRow headerRow = sheet.CreateRow(0);
            Dictionary<string, object> tempDic = new Dictionary<string, object>();
            var dvHelper = sheet.GetDataValidationHelper();
            IRow explainRow = sheet.CreateRow(1);
            ICellStyle explainStyle = workbook.CreateCellStyle(); //说明行格式
            int firstRow = HandleExplainRow(importParam, workbook, explainRow, explainStyle);//处理说明行
            int firstCol = HandleUniqueCol(importParam, headStyle, sheet, headerRow, dvHelper, firstRow);//处理唯一标识列
            int col = 0;
            foreach (var importDef in importDefs)
            {
                if (importDef.ImportRule == 2)
                    continue;
                ICell cell = headerRow.CreateCell(col + firstCol);
                cell.CellStyle = headStyle;
                cell.SetCellValue(importDef.ExcelColName);
                int colWidth = Encoding.GetEncoding(936).GetBytes(importDef.ExcelColName).Length;
                //设置列宽
                sheet.SetColumnWidth(col, (colWidth + 1) * 512);
                if (importParam.IsEnableExplainRow)//启用说明行
                {
                    ICell explainCell = explainRow.CreateCell(col + firstCol);
                    explainCell.CellStyle = explainStyle;
                    explainCell.SetCellValue(importDef.Explain);
                }
                if (!string.IsNullOrEmpty(importDef.ActionID) && importDef.IsHelp) //Excel帮助Sheet页
                {
                    HandleNewHelpTemplate(workbook, sheet, dvHelper, firstRow, importDef, cell);
                }
                else if (!string.IsNullOrEmpty(importDef.DataSource) && importDef.IsHelp) //Excel帮助Sheet页
                {
                    handleOldHelpTemplate(workbook, sheet, tempDic, dvHelper, firstRow, importDef, cell);
                }
                else if (!string.IsNullOrEmpty(importDef.Explain)) //Excel导入说明
                {
                    AddPromptBox(sheet, dvHelper, cell, importDef.Explain);
                }
                col++;
            }

            #endregion
            return workbook;
        }

        /// <summary>
        /// 老版组件设置帮助
        /// </summary>
        public void handleOldHelpTemplate(IWorkbook workbook, ISheet sheet, Dictionary<string, object> tempDic, IDataValidationHelper dvHelper, int firstRow, ImportDef importDef, ICell cell)
        {
            ISheet sdoSheet = workbook.CreateSheet(importDef.ExcelColName);
            sdoSheet.SetColumnWidth(0, 4000);
            ExprResult exprResult = ExprService.GetInstance.JSExecuteNotReport(importDef.DataSource, tempDic);
            if (exprResult.State == ResultState.Fail)
                throw new FasException(ExcelConstCode.ImportFailed, importDef.DataSource + $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_253206119", "执行失败")}" + exprResult.ErrMsg);
            var array = exprResult.Result as TArray<TRecord>;
            AddValidation(array, sheet, firstRow, sdoSheet, dvHelper, cell, importDef);//设置下拉帮助
        }
        /// <summary>
        /// 新版组件设置帮助
        /// </summary>
        public void HandleNewHelpTemplate(IWorkbook workbook, ISheet sheet, IDataValidationHelper dvHelper, int firstRow, ImportDef importDef, ICell cell)
        {
            ISheet sdoSheet = workbook.CreateSheet(importDef.ExcelColName);
            sdoSheet.SetColumnWidth(0, 4000);
            TResult tResult;
            switch (importDef.ActionType)
            {
                case 2:
                    tResult = ActionExecuteService.GetInstance.Execute(importDef.ActionID, Action.SPI.ActionType.Action, importDef.Parameters, true);
                    break;
                case 3:
                    tResult = ActionExecuteService.GetInstance.Execute(importDef.ActionID, Action.SPI.ActionType.DQ, importDef.Parameters, true);
                    break;
                default:
                    throw new FasException(ExcelConstCode.ExportFailed, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1232038745", "不支持的Action类型")}");
            }
            var result = tResult as TCommonResult<TRecord>;
            var array = result.List;
            AddValidation(array, sheet, firstRow, sdoSheet, dvHelper, cell, importDef);//设置下拉帮助
        }

        /// <summary>
        /// 处理唯一标识列
        /// </summary>
        /// <param name="importParam"></param>
        /// <param name="headStyle"></param>
        /// <param name="sheet"></param>
        /// <param name="headerRow"></param>
        /// <param name="dvHelper"></param>
        /// <param name="firstRow"></param>
        /// <returns></returns>
        private int HandleUniqueCol(ImportParam importParam, ICellStyle headStyle, ISheet sheet, IRow headerRow, IDataValidationHelper dvHelper, int firstRow)
        {
            int firstCol = 0;
            if (importParam.IsEnableUniqueCol)//启用标识列
            {
                firstCol = 1;
                ICell cell0 = headerRow.CreateCell(0); //唯一标识
                cell0.CellStyle = headStyle;
                cell0.SetCellValue($"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1318759965", "唯一标识")}");
                AddPromptBox(sheet, dvHelper, cell0, $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_765965389", "Excel行标识用，不能重复")}");
                for (int i = 1; i <= 10; i++)
                {
                    IRow dataRow = sheet.CreateRow(i + firstRow);
                    ICell newCell = dataRow.CreateCell(0);
                    newCell.SetCellValue("A" + i);
                }
            }
            return firstCol;
        }
        /// <summary>
        /// 处理说明行
        /// </summary>
        /// <param name="importParam"></param>
        /// <param name="workbook"></param>
        /// <param name="explainRow"></param>
        /// <param name="explainStyle"></param>
        /// <returns></returns>
        private static int HandleExplainRow(ImportParam importParam, IWorkbook workbook, IRow explainRow, ICellStyle explainStyle)
        {
            int firstRow = 0;
            if (importParam.IsEnableExplainRow)//启用说明行
            {
                firstRow = 1;
                IFont explainFont = workbook.CreateFont();
                explainFont.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
                explainStyle.SetFont(explainFont);
                ICell cell1 = explainRow.CreateCell(0); //填写说明
                cell1.CellStyle = explainStyle;
                cell1.SetCellValue($"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1354271462", "填写说明")}");
            }
            return firstRow;
        }

        /// <summary>
        /// 设置下拉帮助
        /// </summary>
        public void AddValidation(TArray<TRecord> array, ISheet sheet, int firstRow, ISheet sdoSheet, IDataValidationHelper dvHelper, ICell cell, ImportDef importDef)
        {
            if (array == null)
                throw new FasException(ExcelConstCode.DownloadTemplateFailed, importDef.DataSource + $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_309835851", "返回类型必须为集合")}");
            for (int j = 0; j < array.Count; j++)
            {
                IRow dataRow = sdoSheet.CreateRow(j);
                TRecord record = array[j];
                if (string.IsNullOrEmpty(importDef.DisplayField))
                    throw new FasException(ExcelConstCode.DownloadTemplateFailed, importDef.ColName + $"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1380724084", "列需要设置帮助显示字段")}");
                dataRow.CreateCell(0).SetCellValue(Convert.ToString(record[importDef.DisplayField]));
            }
            // 要加下拉列表的范围
            var addressList = new CellRangeAddressList(cell.RowIndex + 1 + firstRow, 65535, cell.ColumnIndex, cell.ColumnIndex);
            // 格式 Sheet2!$A$1:$A$10
            var dvConstraint = dvHelper.CreateFormulaListConstraint($"{sdoSheet.SheetName}!$A$1:$A${array.Count + 1}");
            var validation = dvHelper.CreateValidation(dvConstraint, addressList);
            if (!string.IsNullOrEmpty(importDef.Explain))
            {
                validation.CreatePromptBox($"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1354271462", "填写说明")}", importDef.Explain);
                validation.ShowPromptBox = true;
            }
            // 强制必须填下拉列表给出的值            
            validation.ShowErrorBox = true;
            sheet.AddValidationData(validation);
        }

        /// <summary>
        /// 设置单元格说明
        /// </summary>
        public void AddPromptBox(ISheet sheet, IDataValidationHelper dvHelper, ICell cell, string explain)
        {
            // 提示范围
            var addressList = new CellRangeAddressList(cell.RowIndex, 65535, cell.ColumnIndex, cell.ColumnIndex);
            var dvConstraint = dvHelper.CreateCustomConstraint($"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1829868688", "提示")}");
            var validation = dvHelper.CreateValidation(dvConstraint, addressList);
            validation.CreatePromptBox($"{T.ServiceComponent.I18N.Service.TermService.Current.GetText("T_FAS_fasr_Excel_1354271462", "填写说明")}", explain);
            validation.ShowPromptBox = true;
            sheet.AddValidationData(validation);
        }

        /// <summary>
        /// 获取Excel模板
        /// </summary>
        /// <returns></returns>
        public byte[] GetTemplate(ImportParam importParam, CompositeDataObject cdo, List<ImportDef> importDefs)
        {
            IWorkbook workbook = GetWorkbook(importParam, cdo, importDefs);

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms,false);
                ms.Flush();
                return ms.ToArray();
            }
        }
    }
}